{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd,xlwings as xw "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据导入到pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "### 1.打开对应的工作簿\n",
    "\n",
    "wb_tb=xw.Book(r'C:\\Users\\UUPT\\Downloads\\发生额及余额表 (23).xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "### 2.从工作簿中读取数据到dataframe\n",
    "tb=wb_tb.sheets('第一页').range('c7').current_region.options(pd.DataFrame,index=False,header=False).value\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>科目类别</td>\n",
       "      <td>科目编码</td>\n",
       "      <td>科目名称</td>\n",
       "      <td>期初余额</td>\n",
       "      <td>None</td>\n",
       "      <td>本期发生</td>\n",
       "      <td>None</td>\n",
       "      <td>期末余额</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>借方</td>\n",
       "      <td>贷方</td>\n",
       "      <td>借方</td>\n",
       "      <td>贷方</td>\n",
       "      <td>借方</td>\n",
       "      <td>贷方</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>资产</td>\n",
       "      <td>1002</td>\n",
       "      <td>银行存款</td>\n",
       "      <td>3758142.3</td>\n",
       "      <td>None</td>\n",
       "      <td>220354.93</td>\n",
       "      <td>178537.85</td>\n",
       "      <td>3799959.38</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>资产</td>\n",
       "      <td>100201</td>\n",
       "      <td>招商基本户-0902</td>\n",
       "      <td>61161.0</td>\n",
       "      <td>None</td>\n",
       "      <td>213885.19</td>\n",
       "      <td>178537.85</td>\n",
       "      <td>96508.34</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>资产</td>\n",
       "      <td>100202</td>\n",
       "      <td>兴业一般户-5352</td>\n",
       "      <td>3696981.3</td>\n",
       "      <td>None</td>\n",
       "      <td>6469.74</td>\n",
       "      <td>None</td>\n",
       "      <td>3703451.04</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      0        1            2          3     4          5          6  \\\n",
       "0  科目类别     科目编码         科目名称       期初余额  None       本期发生       None   \n",
       "1  None     None         None         借方    贷方         借方         贷方   \n",
       "2    资产     1002         银行存款  3758142.3  None  220354.93  178537.85   \n",
       "3    资产  　100201  　招商基本户-0902    61161.0  None  213885.19  178537.85   \n",
       "4    资产  　100202  　兴业一般户-5352  3696981.3  None    6469.74       None   \n",
       "\n",
       "            7     8  \n",
       "0        期末余额  None  \n",
       "1          借方    贷方  \n",
       "2  3799959.38  None  \n",
       "3    96508.34  None  \n",
       "4  3703451.04  None  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "##  看一下导入的数据的形状。\n",
    "# 注意：商贸和商贸分的科目余额表的格式居然不同，回头统一成默认的吧。  都改成数量金额式就可以了。\n",
    "\n",
    "tb.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 用Pandas对数据进行处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>科目类别</td>\n",
       "      <td>科目编码</td>\n",
       "      <td>科目名称</td>\n",
       "      <td>期初余额</td>\n",
       "      <td>期初余额</td>\n",
       "      <td>本期发生</td>\n",
       "      <td>本期发生</td>\n",
       "      <td>期末余额</td>\n",
       "      <td>期末余额</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>借方</td>\n",
       "      <td>贷方</td>\n",
       "      <td>借方</td>\n",
       "      <td>贷方</td>\n",
       "      <td>借方</td>\n",
       "      <td>贷方</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>资产</td>\n",
       "      <td>1002</td>\n",
       "      <td>银行存款</td>\n",
       "      <td>3758142.3</td>\n",
       "      <td>None</td>\n",
       "      <td>220354.93</td>\n",
       "      <td>178537.85</td>\n",
       "      <td>3799959.38</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>资产</td>\n",
       "      <td>100201</td>\n",
       "      <td>招商基本户-0902</td>\n",
       "      <td>61161.0</td>\n",
       "      <td>None</td>\n",
       "      <td>213885.19</td>\n",
       "      <td>178537.85</td>\n",
       "      <td>96508.34</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>资产</td>\n",
       "      <td>100202</td>\n",
       "      <td>兴业一般户-5352</td>\n",
       "      <td>3696981.3</td>\n",
       "      <td>None</td>\n",
       "      <td>6469.74</td>\n",
       "      <td>None</td>\n",
       "      <td>3703451.04</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      0        1            2          3     4          5          6  \\\n",
       "0  科目类别     科目编码         科目名称       期初余额  期初余额       本期发生       本期发生   \n",
       "1                                     借方    贷方         借方         贷方   \n",
       "2    资产     1002         银行存款  3758142.3  None  220354.93  178537.85   \n",
       "3    资产  　100201  　招商基本户-0902    61161.0  None  213885.19  178537.85   \n",
       "4    资产  　100202  　兴业一般户-5352  3696981.3  None    6469.74       None   \n",
       "\n",
       "            7     8  \n",
       "0        期末余额  期末余额  \n",
       "1          借方    贷方  \n",
       "2  3799959.38  None  \n",
       "3    96508.34  None  \n",
       "4  3703451.04  None  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tb1=tb\n",
    "\n",
    "### 1.处理表头中的缺失值。表头有两行，一行是名称，一行是借方贷方，需要分别处理。\n",
    "\n",
    "# 1.1 处理第二行\n",
    "\n",
    "tb1.loc[0]=tb.loc[0].fillna(method='ffill')  # 填充空值，向下填充\n",
    "\n",
    "# 1.2 处理第二行\n",
    "\n",
    "tb1.loc[1]=tb.loc[1].fillna('') #这一行的None 填充为空\n",
    "\n",
    "# 1.3 处理后的数据\n",
    "tb1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "tb2=tb1\n",
    "### 2.开始进行合并单元格表头的处理，将多重表头合并为一重表头\n",
    "\n",
    "\n",
    "# 2.1 提升第一行为标题\n",
    "tb2.columns=tb2.loc[0] #将第一行作为列名\n",
    "tb2=tb2[1:]  #删除第一行\n",
    "# 2.2 转置数据\n",
    "tb2=tb2.T.reset_index()\n",
    "# 2.3 将第一列和第二列合并\n",
    "tb2[1]=tb2[0].str.cat(tb2[1])  #将第一列和第二列合并\n",
    "tb2=tb2.drop([0],axis=1) #删除多出来的 第0列。\n",
    "# 2.4 将数据转置回来\n",
    "tb2=tb2.set_index(1).T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>1</th>\n",
       "      <th>科目类别</th>\n",
       "      <th>科目编码</th>\n",
       "      <th>科目名称</th>\n",
       "      <th>期初余额借方</th>\n",
       "      <th>期初余额贷方</th>\n",
       "      <th>本期发生借方</th>\n",
       "      <th>本期发生贷方</th>\n",
       "      <th>期末余额借方</th>\n",
       "      <th>期末余额贷方</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>资产</td>\n",
       "      <td>1002</td>\n",
       "      <td>银行存款</td>\n",
       "      <td>3758142.3</td>\n",
       "      <td>None</td>\n",
       "      <td>220354.93</td>\n",
       "      <td>178537.85</td>\n",
       "      <td>3799959.38</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>资产</td>\n",
       "      <td>100201</td>\n",
       "      <td>招商基本户-0902</td>\n",
       "      <td>61161.0</td>\n",
       "      <td>None</td>\n",
       "      <td>213885.19</td>\n",
       "      <td>178537.85</td>\n",
       "      <td>96508.34</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>资产</td>\n",
       "      <td>100202</td>\n",
       "      <td>兴业一般户-5352</td>\n",
       "      <td>3696981.3</td>\n",
       "      <td>None</td>\n",
       "      <td>6469.74</td>\n",
       "      <td>None</td>\n",
       "      <td>3703451.04</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>资产</td>\n",
       "      <td>1012</td>\n",
       "      <td>其他货币资金</td>\n",
       "      <td>223856.82</td>\n",
       "      <td>None</td>\n",
       "      <td>6102.0</td>\n",
       "      <td>28084.38</td>\n",
       "      <td>201874.44</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>资产</td>\n",
       "      <td>101201</td>\n",
       "      <td>支付宝</td>\n",
       "      <td>21982.38</td>\n",
       "      <td>None</td>\n",
       "      <td>6102.0</td>\n",
       "      <td>28084.38</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "1 科目类别     科目编码         科目名称     期初余额借方 期初余额贷方     本期发生借方     本期发生贷方  \\\n",
       "2   资产     1002         银行存款  3758142.3   None  220354.93  178537.85   \n",
       "3   资产  　100201  　招商基本户-0902    61161.0   None  213885.19  178537.85   \n",
       "4   资产  　100202  　兴业一般户-5352  3696981.3   None    6469.74       None   \n",
       "5   资产     1012       其他货币资金  223856.82   None     6102.0   28084.38   \n",
       "6   资产  　101201         　支付宝   21982.38   None     6102.0   28084.38   \n",
       "\n",
       "1      期末余额借方 期末余额贷方  \n",
       "2  3799959.38   None  \n",
       "3    96508.34   None  \n",
       "4  3703451.04   None  \n",
       "5   201874.44   None  \n",
       "6        None   None  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tb2.head()  #看一下tb2,到tb2，都是通用的处理，资产负债表说明，部门收支表都能在tb2的基础上做，下面可以做 资产负债表说明 和 部门收支表了。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 制作资产负债表说明\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1.筛选资产、负债、权益类科目、逆透视期末余额借方，期末余额贷方两列\n",
    "\n",
    "tb3=(\n",
    "    tb2[tb2['科目类别'].isin(['资产','负债','权益'])] #筛选资产、负债、权益类科目\n",
    "    .fillna(0)\n",
    "    .assign(余额借方= lambda x: x['期末余额借方']-x['期末余额贷方'])\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>1</th>\n",
       "      <th>科目类别</th>\n",
       "      <th>科目编码</th>\n",
       "      <th>科目名称</th>\n",
       "      <th>期初余额借方</th>\n",
       "      <th>期初余额贷方</th>\n",
       "      <th>本期发生借方</th>\n",
       "      <th>本期发生贷方</th>\n",
       "      <th>期末余额借方</th>\n",
       "      <th>期末余额贷方</th>\n",
       "      <th>余额借方</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>资产</td>\n",
       "      <td>1002</td>\n",
       "      <td>银行存款</td>\n",
       "      <td>3758142.30</td>\n",
       "      <td>0.00</td>\n",
       "      <td>220354.93</td>\n",
       "      <td>178537.85</td>\n",
       "      <td>3799959.38</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3799959.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>资产</td>\n",
       "      <td>100201</td>\n",
       "      <td>招商基本户-0902</td>\n",
       "      <td>61161.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>213885.19</td>\n",
       "      <td>178537.85</td>\n",
       "      <td>96508.34</td>\n",
       "      <td>0.00</td>\n",
       "      <td>96508.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>资产</td>\n",
       "      <td>100202</td>\n",
       "      <td>兴业一般户-5352</td>\n",
       "      <td>3696981.30</td>\n",
       "      <td>0.00</td>\n",
       "      <td>6469.74</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3703451.04</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3703451.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>资产</td>\n",
       "      <td>1012</td>\n",
       "      <td>其他货币资金</td>\n",
       "      <td>223856.82</td>\n",
       "      <td>0.00</td>\n",
       "      <td>6102.00</td>\n",
       "      <td>28084.38</td>\n",
       "      <td>201874.44</td>\n",
       "      <td>0.00</td>\n",
       "      <td>201874.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>资产</td>\n",
       "      <td>101201</td>\n",
       "      <td>支付宝</td>\n",
       "      <td>21982.38</td>\n",
       "      <td>0.00</td>\n",
       "      <td>6102.00</td>\n",
       "      <td>28084.38</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251</th>\n",
       "      <td>权益</td>\n",
       "      <td>4001</td>\n",
       "      <td>实收资本</td>\n",
       "      <td>0.00</td>\n",
       "      <td>500000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>500000.00</td>\n",
       "      <td>-500000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252</th>\n",
       "      <td>权益</td>\n",
       "      <td>400102</td>\n",
       "      <td>郑州时空隧道信息技术有限公司</td>\n",
       "      <td>0.00</td>\n",
       "      <td>500000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>500000.00</td>\n",
       "      <td>-500000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>253</th>\n",
       "      <td>权益</td>\n",
       "      <td>4103</td>\n",
       "      <td>本年利润</td>\n",
       "      <td>191457.80</td>\n",
       "      <td>0.00</td>\n",
       "      <td>32528.12</td>\n",
       "      <td>0.00</td>\n",
       "      <td>223985.92</td>\n",
       "      <td>0.00</td>\n",
       "      <td>223985.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254</th>\n",
       "      <td>权益</td>\n",
       "      <td>4104</td>\n",
       "      <td>利润分配</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1380586.38</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1380586.38</td>\n",
       "      <td>-1380586.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255</th>\n",
       "      <td>权益</td>\n",
       "      <td>410406</td>\n",
       "      <td>未分配利润</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1380586.38</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1380586.38</td>\n",
       "      <td>-1380586.38</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>252 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "1   科目类别     科目编码             科目名称      期初余额借方      期初余额贷方     本期发生借方  \\\n",
       "2     资产     1002             银行存款  3758142.30        0.00  220354.93   \n",
       "3     资产  　100201      　招商基本户-0902    61161.00        0.00  213885.19   \n",
       "4     资产  　100202      　兴业一般户-5352  3696981.30        0.00    6469.74   \n",
       "5     资产     1012           其他货币资金   223856.82        0.00    6102.00   \n",
       "6     资产  　101201             　支付宝    21982.38        0.00    6102.00   \n",
       "..   ...      ...              ...         ...         ...        ...   \n",
       "251   权益     4001             实收资本        0.00   500000.00       0.00   \n",
       "252   权益  　400102  　郑州时空隧道信息技术有限公司        0.00   500000.00       0.00   \n",
       "253   权益     4103             本年利润   191457.80        0.00   32528.12   \n",
       "254   权益     4104             利润分配        0.00  1380586.38       0.00   \n",
       "255   权益  　410406           　未分配利润        0.00  1380586.38       0.00   \n",
       "\n",
       "1       本期发生贷方      期末余额借方      期末余额贷方        余额借方  \n",
       "2    178537.85  3799959.38        0.00  3799959.38  \n",
       "3    178537.85    96508.34        0.00    96508.34  \n",
       "4         0.00  3703451.04        0.00  3703451.04  \n",
       "5     28084.38   201874.44        0.00   201874.44  \n",
       "6     28084.38        0.00        0.00        0.00  \n",
       "..         ...         ...         ...         ...  \n",
       "251       0.00        0.00   500000.00  -500000.00  \n",
       "252       0.00        0.00   500000.00  -500000.00  \n",
       "253       0.00   223985.92        0.00   223985.92  \n",
       "254       0.00        0.00  1380586.38 -1380586.38  \n",
       "255       0.00        0.00  1380586.38 -1380586.38  \n",
       "\n",
       "[252 rows x 10 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tb3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2 处理余额列，将方向不对的金额改掉\n",
    "def 处理余额值(df):\n",
    "    if df['科目类别']=='资产':  #资产类科目\n",
    "        df['方向']='借方'\n",
    "        df['余额']=df['余额借方']\n",
    "    else:  #负债和权益类科目\n",
    "        df['方向']='贷方'\n",
    "        df['余额']=df['余额借方']*-1  #余额借方*-1 就等于贷方的余额\n",
    "\n",
    "\n",
    "    return df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''到这一步，资产负债表说明基本是可以用的了'''\n",
    "\n",
    "xw.view(tb3.apply(处理余额值,axis=1))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "\n",
    "# 生成一列部门列，将部门列与科目名称列从一列拆成了两列了，方便使用sumifs\n",
    "tb['部门']=tb.apply(lambda row:row ['科目名称'] if pd.isnull(row['科目编码']) else '',axis=1).str.strip()\n",
    "# 生成一列科目名称列\n",
    "tb['科目名称2']=tb.apply(lambda row:pd.NA if pd.isnull(row['科目编码']) else row ['科目名称'],axis=1)\n",
    "tb['科目名称2'].fillna(method='ffill',inplace=True)  # 填充缺失值#\n",
    "\n",
    "wb_tb.sheets('数据源').range('A1').value=tb\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 取出来科目来，作为部门收支表的index。\n",
    "\n",
    "科目表=tb.loc[tb['科目编码'].notna(),['科目编码','科目名称']].query('科目编码 != None')\n",
    "科目表['科目级别']=科目表['科目编码'].str.strip().str.len().map({4.0:1,6.0:2,8.0:3})\n",
    "\n",
    "# 创建是否末级列\n",
    "# 将科目级别的值都上移一行，创建是否末级\n",
    "科目表['科目级别2']=科目表['科目级别'].shift(-1)\n",
    "科目表['是否末级']=科目表.apply(lambda row: '否' if row['科目级别2']> row['科目级别'] else '是',axis=1)\n",
    "\n",
    "# 将数据写入到excel\n",
    "wb_tb.sheets('部门收支表').range('a2').value=科目表.loc[:,['科目编码','科目级别','科目名称','是否末级']]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### 这个是做 部门收支表时候用的，做资产负债表说明用不到，是一个拆科目列的东西\n",
    "\n",
    "tb3=tb2\n",
    "### 3.将辅助核算项从科目中拆分出来 \n",
    "\n",
    "# 取出原科目名称列中的辅助核算项\n",
    "tb3['部门']=tb3.apply(lambda row:row ['科目名称'] if pd.isnull(row['科目编码']) else '',axis=1).str.strip()\n",
    "\n",
    "# 取出原科目名称列中的科目名称\n",
    "tb3['科目名称2']=tb3.apply(lambda row:pd.NA if pd.isnull(row['科目编码']) else row ['科目名称'],axis=1)\n",
    "tb3['科目名称2'].fillna(method='ffill',inplace=True)  # 填充缺失值#"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
